﻿Imports KrosLib.Editors
Imports System.Data
Imports System.IO
Imports amod2


Public Class DBGen

    Dim mScriptDatabase As String = ""
    Dim mScriptTables As String = ""
    Dim mScriptSystem As String = ""

    Public Property ScriptSystem() As String
        Get
            Return mScriptSystem
        End Get
        Set(ByVal Value As String)
            mScriptSystem = Value
        End Set
    End Property

    Public Property ScriptDatabaseCreate() As String
        Get
            Return mScriptDatabase
        End Get
        Set(ByVal Value As String)
            mScriptDatabase = Value
        End Set
    End Property

    Public Property ScriptTablesCreate() As String
        Get
            Return mScriptTables
        End Get
        Set(ByVal Value As String)
            mScriptTables = Value
        End Set
    End Property

    ''' <summary>
    ''' vrati skript na vygenerovanie komplet databazy pre dane moduli
    ''' </summary>
    ''' <param name="pDbGenSettings">nastavenie generatora</param>
    ''' <param name="pModules">zoznam modulov pre ktore sa bude generovat skript</param>
    ''' <returns>skript na vygenerovanie databazy</returns>
    ''' <remarks></remarks>
    Public Function generateWholeDatabaseScript(ByVal pDbGenSettings As DBGenSettings, ByVal pModules As List(Of ModulAtributes)) As String
        Dim lOwner As String = "dbo" ' db owner
        Dim lDat As Dat                     ' dat vytvoreni nacitanim jednoho aMod suboru
        Dim lOneDataset As DsAModOneVersion ' dataset jednej verzie z ktoreho sa bude generovat databaza
        Dim lVersion As String
        Dim lSystemsSQLscripts As String = ""
        Dim lFinalTablesScript As String = ""
        Dim lResultScript As String
        Dim lDbCreateScript As String   ' skript na vytvorenie prazdnej databazy
        Dim lOneTableScript As String = ""
        Dim lPath1, lPath2 As String
        lPath1 = Path.Combine(pDbGenSettings.DbPath, pDbGenSettings.DBFileName + ".mdf")
        lPath2 = Path.Combine(pDbGenSettings.DbPath, pDbGenSettings.DBFileName + "_log.ldf")
        ' vytvorim si cestu kam sa ulozi databaza
        lDbCreateScript = "CREATE DATABASE [" + pDbGenSettings.DbName + "] ON  PRIMARY" & vbCrLf & "( NAME = N'" + pDbGenSettings.DbName + "', FILENAME = N'" + lPath1 + "')" & vbCrLf & "LOG ON" & vbCrLf & "( NAME = N'" + pDbGenSettings.DbName + "_log', FILENAME = N'" + lPath2 + "')"
        Me.mScriptDatabase = lDbCreateScript
        ' nacita sa do "lSystemsSQLscripts" SQL systemove skripty ktore sa spustia na serveri po vytvoreni vygenerovaneho skriptu na vytvorenie databazy
        If (pDbGenSettings.SystSQLscriptsPath.Length > 0) Then
            Dim strReader As StreamReader = New StreamReader(pDbGenSettings.SystSQLscriptsPath)
            lSystemsSQLscripts = strReader.ReadToEnd()
        End If

        ' pre kazdy modul sa vytvoria SQL skripty na vytvorenie tabuliek ktore sa na konci spoja do jedneho skriptu
        For Each lOneModule As ModulAtributes In pModules
            lOneTableScript = ""
            ' vytvori sa novy objekt typu Dat
            lDat = New Dat(lOneModule.mModulName, lOneModule.mHumanModulName, lOneModule.mPath) 'lala *** 
            ' nacita sa Amod subor do lDat
            lDat.LoadAMod(lOneModule.mPath, "CHYBA - Nepodarilo sa otvoriť Amod súbor pri generovaní databázy.")

            ' nastavi sa verzia a nacita konkretny dataset podla verzie
            If (pDbGenSettings.Ver = -1) Then
                lVersion = lDat.GetLastVersion.ToString()
                lOneDataset = lDat.GetVersionDataset(lVersion) 'ak ide o poslednu verziu
            Else
                lVersion = pDbGenSettings.Ver
                lOneDataset = lDat.GetVersionDataset(lVersion)
            End If

            For Each aModDataRow As DataRow In lOneDataset.Tables(xModDataSet.TableSysnames.TableList.Name).Rows ' pre kazdu tabulku v TableList

                Dim nazovTabulky As String = aModDataRow(xModDataSet.TableSysnames.TableList.Columns.TableName)
                lOneTableScript += vbCrLf & "CREATE TABLE " & pDbGenSettings.DbName & "." & lOwner & "." & nazovTabulky & " ("
                Dim lAtrCount As Integer = lOneDataset.Tables(nazovTabulky).Rows.Count
                For Each aModDataRow2 As DataRow In lOneDataset.Tables(nazovTabulky).Rows
                    If (aModDataRow2(xModDataSet.TableSysnames.DataTable.Columns.ColumnType) = 0) Then
                        lOneTableScript += vbCrLf & returnScriptForOneAttribute(aModDataRow2)
                    End If
                Next
                lOneTableScript = lOneTableScript.Remove(lOneTableScript.Length - 1, 1)
                lOneTableScript += ")" & vbCrLf
            Next

            lFinalTablesScript += vbCrLf & lOneTableScript
        Next
        Me.mScriptTables = lFinalTablesScript ' vsetky skripty na vytvorenie vsetkych tabuliek a atributov
        Me.mScriptSystem = lSystemsSQLscripts
        lResultScript = vbCrLf & lDbCreateScript & vbCrLf & lFinalTablesScript & vbCrLf & lSystemsSQLscripts
        Return lResultScript
    End Function

    ''' <summary>
    ''' vrati skript pre jeden atribut
    ''' </summary>
    ''' <param name="aModDataRow">jeden riadok ktory reprezentuje jeden atribut</param>
    ''' <returns>SQL skript na vytvorenie jedneho atributu</returns>
    ''' <remarks></remarks>
    Private Function returnScriptForOneAttribute(ByVal aModDataRow As DataRow) As String
        Dim oneAttribusScript As String = ""

        If (aModDataRow(xModDataSet.TableSysnames.DataTable.Columns.ColumnType) = 0) Then
            Dim name As String = aModDataRow(xModDataSet.TableSysnames.DataTable.Columns.ColumnName)
            Dim dataType As String = aModDataRow(xModDataSet.TableSysnames.DataTable.Columns.DataType)
            Dim allowNull As String = aModDataRow(xModDataSet.TableSysnames.DataTable.Columns.AllowNull)
            Dim pk As String = aModDataRow(xModDataSet.TableSysnames.DataTable.Columns.System)
            Dim len As String = aModDataRow(xModDataSet.TableSysnames.DataTable.Columns.Length)
            Dim decimals As String = aModDataRow(xModDataSet.TableSysnames.DataTable.Columns.Decimals)

            oneAttribusScript = " " + name
            Dim dataTypeAsInt As Integer = Convert.ToInt32(dataType)

            Dim sqlDataType As SqlDbType = convertAmodDataTypeToSQLDBType(CType([Enum].Parse(GetType(KrosLib.Data.eDataType), [Enum].GetName(GetType(KrosLib.Data.eDataType), dataTypeAsInt)), KrosLib.Data.eDataType))

            oneAttribusScript += " " + sqlDataType.ToString()
            ' ak ide o typ decimal kde sa urcuje dlzka
            If (sqlDataType = SqlDbType.Decimal) Then
                oneAttribusScript += "(" + len + "," + decimals + ")"
            End If
            ' ak ide o typ varchar kde sa urcuje dlzka
            If (sqlDataType = SqlDbType.VarChar) Then
                oneAttribusScript += "(" + len + ")"
            End If

            If (allowNull = "0") Then
                oneAttribusScript = oneAttribusScript & " NOT NULL"
            End If
            If (pk = "1") Then
                oneAttribusScript = oneAttribusScript & " PRIMARY KEY"
            End If
        End If
        Return oneAttribusScript + ","
    End Function

    ''' <summary>
    ''' konvertuje Amod Data Typ na Sql DB Typ
    ''' </summary>
    ''' <param name="AmodDataType">AmodDataType As KrosLib.Data.eDataType</param>
    ''' <returns>SqlDbType</returns>
    ''' <remarks></remarks>
    Private Function convertAmodDataTypeToSQLDBType(ByVal AmodDataType As KrosLib.Data.eDataType) As SqlDbType

        Select Case (AmodDataType)
            Case (KrosLib.Data.eDataType.Boolean)
                Return (SqlDbType.Bit)          'integer,bigint
            Case (KrosLib.Data.eDataType.Memo), (KrosLib.Data.eDataType.Text)
                Return (SqlDbType.VarChar)  'memo dat na text alebo string alebo co to je v SQL
            Case (KrosLib.Data.eDataType.Decimal)
                Return (SqlDbType.Decimal)
            Case (KrosLib.Data.eDataType.Int32)
                Return (SqlDbType.Int)
            Case (KrosLib.Data.eDataType.Int64)
                Return (SqlDbType.BigInt)
            Case (KrosLib.Data.eDataType.Date)
                Return (SqlDbType.DateTime)
            Case (KrosLib.Data.eDataType.Time)
                Return (SqlDbType.DateTime)
            Case (KrosLib.Data.eDataType.DateTime)
                Return (SqlDbType.DateTime)
            Case (KrosLib.Data.eDataType.Binary)
                Return (SqlDbType.Binary)
            Case Else

                Throw New Exception("Chyba pri konverzii typov")
        End Select

        Return SqlDbType.Text

    End Function

End Class
